/**
 * 
 */
package com.py.credit.utils;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;

import com.py.credit.entity.AreaName;
import com.py.credit.entity.CollectionForm;
import com.py.credit.entity.Column;
import com.py.credit.entity.TableResult;
import com.py.credit.entity.common.OptionalColumn;

/**
 * @author dd
 *
 */
public class ExcelUtil {
	private static final Logger LOG = LoggerFactory.getLogger(ExcelUtil.class);

	/**
	 * 
	 * @param response
	 * @param data
	 *            导出数据
	 * @param fileNanme
	 *            文件名
	 * @param sheetName
	 *            sheet名
	 * @param showColumns
	 *            展示列
	 * @param allColumns
	 *            列全部信息
	 * @throws IOException
	 */
	public static void downloadExcel(HttpServletResponse response, List<Map<String, Object>> data, String fileNanme,
			String sheetName, List<String> showColumns, List<OptionalColumn> allColumns) throws IOException {

		response.setContentType("application/vnd.ms-excel");
		XSSFWorkbook workbook = null;
		OutputStream fOut = null;
		// 进行转码，使其支持中文文件名
		fileNanme = URLEncoder.encode(fileNanme, "UTF-8");
		response.setHeader("content-disposition", "attachment;filename=" + fileNanme + ".xlsx");
		// 产生工作簿对象
		workbook = new XSSFWorkbook();
		// 产生工作表对象
		XSSFSheet sheet = workbook.createSheet();
		workbook.setSheetName(0, sheetName);
		XSSFRow row = null;
		XSSFCell cell = null;
		List<String> strBT = new ArrayList<String>();
		for (String colEN : showColumns) {
			for (OptionalColumn col : allColumns) {
				if (colEN.equals(col.getColNameEN())) {
					strBT.add(col.getColNameCN());
					break;
				}
			}
		}

		// 创建标题行
		row = sheet.createRow(0);
		for (int i = 0; i < strBT.size(); i++) {
			cell = row.createCell(i);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(strBT.get(i));
		}
		for (int i = 0; i < data.size(); i++) {
			row = sheet.createRow(i + 1);// 创建一行
			for (int j = 0; j < strBT.size(); j++) {
				cell = row.createCell(j);// 创建一列
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				String showColEN = showColumns.get(j);
				Object object = data.get(i).get(showColEN);
				String widget = null;
				String type = null;
				Map<String, String> widgetValue = null;
				for (OptionalColumn col : allColumns) {
					if (showColEN.equals(col.getColNameEN())) {
						widget = col.getWidget();
						widgetValue = col.getWidgetValue();
						type = col.getType();
						break;
					}
				}

				try {

					if (object != null) {

						String columValue = object.toString();

						if ("select".equals(widget) && widgetValue != null) {

							columValue = widgetValue.get(columValue) != null ? widgetValue.get(columValue) : "";
						} else if ("timestamp".equals(type) && columValue.lastIndexOf(".") != -1) {

							columValue = columValue.substring(0, columValue.lastIndexOf("."));

						}
						cell.setCellValue(columValue);

					}
				} catch (Exception e) {
					LOG.error("数据导出错:" + showColEN + "====>", e);
				}
			}

		}

		fOut = response.getOutputStream();
		workbook.write(fOut);
		workbook.close();
		fOut.flush();
		fOut.close();

	}

	/**
	 * @param response
	 * @param table
	 * @throws IOException
	 */
	public static void downloadTemplate(HttpServletResponse response, TableResult table) throws IOException {
		response.setContentType("application/vnd.ms-excel");
		HSSFWorkbook workbook = null;
		OutputStream fOut = null;
		// 进行转码，使其支持中文文件名
		String fileNanme = URLEncoder.encode(table.getTableNameCN(), "UTF-8");
		response.setHeader("content-disposition", "attachment;filename=" + fileNanme + ".xls");
		// 产生工作簿对象
		workbook = new HSSFWorkbook();
		// 产生工作表对象
		HSSFSheet sheet = workbook.createSheet();
		//得到一个poi工具
		CreationHelper factory = workbook.getCreationHelper();  
				// 得到换图对象
		Drawing drawing = sheet.createDrawingPatriarch(); 
				//设置注释位置
		ClientAnchor anchor = factory.createClientAnchor(); 
		workbook.setSheetName(0, table.getTableNameCN());
		HSSFFont headfont = workbook.createFont();
		headfont.setFontName("宋体");
		headfont.setFontHeightInPoints((short) 13);// 字体大小
		headfont.setBold(true);
		HSSFCellStyle headstyle = workbook.createCellStyle();
		headstyle.setFont(headfont);
		headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
		headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
		headstyle.setLocked(true);
		// headstyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// headstyle.setFillBackgroundColor(HSSFColor.GREY_40_PERCENT.index);
		// headstyle.setLeftBorderColor(HSSFColor.BLACK.index);

		HSSFRow row = null;
		HSSFCell cell = null;
		row = sheet.createRow(0);

		if (!CollectionUtils.isEmpty(table.getColumns())) {
			for (int i = 0; i < table.getColumns().size(); i++) {
				sheet.setColumnWidth(i, 8000);
				cell = row.createCell(i);
				cell.setCellStyle(headstyle);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(table.getColumns().get(i).getMetricName());
				Comment comment = drawing.createCellComment(anchor);  
			    RichTextString str = factory.createRichTextString(table.getColumns().get(i).getTips());  
			    comment.setString(str);  
//			    comment.setAuthor("Apache POI");  
			    cell.setCellComment(comment);  
			}
		}

		fOut = response.getOutputStream();
		workbook.write(fOut);
		workbook.close();
		fOut.flush();
		fOut.close();

	}

	public static void creatFrontWorkflowBisExcel(String fileName, List<Map<String, String>> data, String dest,
			String[] btColumns) throws IOException {

		HSSFWorkbook workbook = null;

		// 产生工作簿对象
		workbook = new HSSFWorkbook();
		// 产生工作表对象
		HSSFSheet sheet = workbook.createSheet();
		HSSFRow row = null;
		HSSFCell cell = null;
		row = sheet.createRow(0);
		for (int i = 0; i < btColumns.length; i++) {
			cell = row.createCell(i);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(btColumns[i]);
		}

		for (int i = 0; i < data.size(); i++) {
			row = sheet.createRow(i + 1);// 创建一行
			int cellIndex = 0;
			for (Entry<String, String> entry : data.get(i).entrySet()) {
				cell = row.createCell(cellIndex);// 创建一列
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				Object object = entry.getValue();
				try {
					if (object != null) {

						String columValue = object.toString();
						cell.setCellValue(columValue);

					}
				} catch (Exception e) {
					LOG.error("数据导出错:" + entry.getKey() + "====>", e);
				}

				cellIndex++;
			}

		}

		File dir = new File(dest);

		if (!dir.exists()) {

			dir.mkdirs();

		}

		FileOutputStream fileOutputStream = new FileOutputStream(dest + File.separator + fileName);

		workbook.write(fileOutputStream);

		workbook.close();

		fileOutputStream.close();
	}

	public static void downloadDataforDeptId(HttpServletResponse response, Map<Integer, List<CollectionForm>> colMap,
			AreaName area, List<CollectionForm> deptList, Map<Integer, String> areaMap) throws IOException {
		response.setContentType("application/vnd.ms-excel");
		HSSFWorkbook workbook = null;
		OutputStream fOut = null;
		// 进行转码，使其支持中文文件名
		String fileNanme = URLEncoder.encode("默认", "UTF-8");
		String areaName = "1";
		if (area != null) {
			fileNanme = URLEncoder.encode(area.getParentName(), "UTF-8");
		}

		response.setHeader("content-disposition", "attachment;filename=" + fileNanme + ".xls");
		// 产生工作簿对象
		workbook = new HSSFWorkbook();
		// 产生工作表对象
		for (int j = 0; j < deptList.size(); j++) {
			HSSFSheet sheet = workbook.createSheet();
			if (deptList.get(j).getDeptName() != null) {
				workbook.setSheetName(j, deptList.get(j).getDeptName());
			} else {
				workbook.setSheetName(j, String.valueOf(j));
			}

			HSSFRow row = null;
			HSSFCell cell = null;
			row = sheet.createRow(0);
			List<String> tableTopList = new ArrayList<String>();
			tableTopList.add("序号");
			tableTopList.add("区域");
			tableTopList.add("部门");
			tableTopList.add("信息资源表");
			tableTopList.add("截止时间");
			tableTopList.add("入库量");
			for (int i = 0; i < tableTopList.size(); i++) {
				cell = row.createCell(i);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(tableTopList.get(i));
			}
			List<CollectionForm> list = colMap.get(deptList.get(j).getDeptId());
			if (!CollectionUtils.isEmpty(list)) {
				for (int i = 0; i < list.size(); i++) {
					row = sheet.createRow(i + 1);
					cell.setCellType(HSSFCell.CELL_TYPE_STRING);
					List<String> tableList = new ArrayList<String>();
					tableList.add(String.valueOf(i + 1));
					tableList.add(areaMap.get(deptList.get(j).getDeptId()));
					tableList.add(deptList.get(j).getDeptName());
					tableList.add(list.get(i).getTableName());
					tableList.add(String.valueOf(list.get(i).getCreditTime()));
					tableList.add(String.valueOf(list.get(i).getNum()));
					for (int x = 0; x < tableList.size(); x++) {
						cell = row.createCell(x);
						cell.setCellValue(tableList.get(x));

					}

				}
			}
		}

		fOut = response.getOutputStream();
		workbook.write(fOut);
		workbook.close();
		fOut.flush();
		fOut.close();

	}

	public static void downloadExcelDetail(HttpServletResponse response, List<Map<String, Object>> data, String fileNanme,
			String sheetName, List<String> showColumns,  List<Column> allColumns) throws Exception {
		response.setContentType("application/vnd.ms-excel");
		HSSFWorkbook workbook = null;
		OutputStream fOut = null;
		// 进行转码，使其支持中文文件名
		fileNanme = URLEncoder.encode(fileNanme, "UTF-8");
		response.setHeader("content-disposition", "attachment;filename=" + fileNanme + ".xls");
		// 产生工作簿对象
		workbook = new HSSFWorkbook();
		// 产生工作表对象
		HSSFSheet sheet = workbook.createSheet();
		workbook.setSheetName(0, sheetName);
		HSSFRow row = null;
		HSSFCell cell = null;
		List<String> strBT = new ArrayList<String>();
		for (String colEN : showColumns) {
			for (Column col : allColumns) {
				if (colEN.equals(col.getMetricCode())) {
					strBT.add(col.getMetricName());
					break;
				}
			}
		}

		// 创建标题行
		row = sheet.createRow(0);
		for (int i = 0; i < strBT.size(); i++) {
			cell = row.createCell(i);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(strBT.get(i));
		}
		for (int i = 0; i < data.size(); i++) {
			row = sheet.createRow(i + 1);// 创建一行
			for (int j = 0; j < strBT.size(); j++) {
				cell = row.createCell(j);// 创建一列
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				String showColEN = showColumns.get(j);
				Object object = data.get(i).get(showColEN);
				String widget = null;
				String type = null;
				Map<String, String> widgetValue = null;
				for (Column col : allColumns) {
					if (showColEN.equals(col.getMetricCode())) {
						widget = "";
						widgetValue = new HashMap<String, String>();
						type = col.getFieldType();
						break;
					}
				}

				try {

					if (object != null) {

						String columValue = object.toString();

						if ("select".equals(widget) && widgetValue != null) {

							columValue = widgetValue.get(columValue) != null ? widgetValue.get(columValue) : "";
						} else if ("timestamp".equals(type) && columValue.lastIndexOf(".") != -1) {

							columValue = columValue.substring(0, columValue.lastIndexOf("."));

						}else if("datetime".equals(type)) {
							columValue = DateUtil.getInstance().parseDateStr(columValue, "yyyy-MM-dd");
						}
						cell.setCellValue(columValue);

					}
				} catch (Exception e) {
					LOG.error("数据导出错:" + showColEN + "====>", e);
				}
			}

		}

		fOut = response.getOutputStream();
		workbook.write(fOut);
		workbook.close();
		fOut.flush();
		fOut.close();
	}
	
}
